package edu.ncsu.csc.itrust.dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import edu.ncsu.csc.itrust.DBUtil;
import edu.ncsu.csc.itrust.beans.PatientReferralBean;
import edu.ncsu.csc.itrust.beans.loaders.PatientReferralBeanLoader;
import edu.ncsu.csc.itrust.dao.DAOFactory;
import edu.ncsu.csc.itrust.exception.DBException;

/**
 * Used to update referrals, and fetch lists of referrals sent to and
 * from HCPs.
 *
 * DAO stands for Database Access Object. All DAOs are intended to be reflections of the database, that is,
 * one DAO per table in the database (most of the time). For more complex sets of queries, extra DAOs are
 * added. DAOs can assume that all data has been validated and is correct.
 * 
 * DAOs should never have setters or any other parameter to the constructor than a factory. All DAOs should be
 * accessed by DAOFactory (@see {@link DAOFactory}) and every DAO should have a factory - for obtaining JDBC
 * connections and/or accessing other DAOs.
 * 
 */

public class PatientReferralDAO {
	
	private DAOFactory factory;
	private PatientReferralBeanLoader patientReferralLoader;
	
	/**
	 * The typical constructor.
	 * @param factory The {@link DAOFactory} associated with this DAO, which is used for obtaining SQL connections, etc.
	 */
	public PatientReferralDAO(DAOFactory factory) {
		this.factory = factory;
		patientReferralLoader = new PatientReferralBeanLoader(); 
	}
	
	/**
	 * Returns the patient referral for a given ID
	 * 
	 * @param id The ID of the referral.
	 * @return A PatientReferralBean representing the referral.
	 * @throws DBException
	 */
	public PatientReferralBean getPatientReferral(int id) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM PatientReferrals WHERE ID = ?");
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return patientReferralLoader.loadSingle(rs);
			} else
				return null;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	/**
	 * Gets a list of all referrals sent from an HCP
	 * @param mid The HCP's mid.
	 * @return The list of the referrals they sent.
	 * @throws DBException
	 */
	public List<PatientReferralBean> getReferralsSentFrom(long mid) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM PatientReferrals WHERE SenderID = ?");
			ps.setLong(1, mid);
			ResultSet rs = ps.executeQuery();		
			return patientReferralLoader.loadList(rs);
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Gets a list of all referrals for an office Visit
	 * @param ovid The office visit mid.
	 * @return The list of the referrals for an office visit.
	 * @throws DBException
	 */
	public List<PatientReferralBean> getReferralsForOfficeVisit(long ovid) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM PatientReferrals WHERE VisitID = ?");
			ps.setLong(1, ovid);
			ResultSet rs = ps.executeQuery();		
			return patientReferralLoader.loadList(rs);
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Gets a list of all referrals regarding a particular patient
	 * @param mid The Patients mid.
	 * @return The list of the referrals they sent.
	 * @throws DBException
	 */
	public List<PatientReferralBean> getReferralsForPatient(long mid) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM PatientReferrals WHERE PatientID = ?");
			ps.setLong(1, mid);
			ResultSet rs = ps.executeQuery();		
			return patientReferralLoader.loadList(rs);	
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Gets a list of all referrals sent to an HCP
	 * @param mid The HCP's mid.
	 * @return The list of the referrals sent to them.
	 * @throws DBException
	 */
	public List<PatientReferralBean> getReferralsSentTo(long mid) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("SELECT * FROM PatientReferrals WHERE ReceiverID = ? ORDER BY priority ASC");
			ps.setLong(1, mid);
			ResultSet rs = ps.executeQuery();	
			return patientReferralLoader.loadList(rs);
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Adds a given referral to the database.
	 * @param r The referral to add.
	 * @throws DBException
	 */
	public long addReferral(PatientReferralBean r) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("INSERT INTO PatientReferrals (PatientID,SenderID,ReceiverID,"
					+ "VisitID,Priority,ReferralDetails,Been_Read,AddedDate)  "
					+ "VALUES (?,?,?,?,?,?,?,?)");
			patientReferralLoader.loadParameters(ps, r);
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * edits a given referral in the database.
	 * @param r The referral to update.
	 * @throws DBException
	 */
	public long editReferral(PatientReferralBean r) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("UPDATE PatientReferrals SET PatientID=?,SenderID=?,ReceiverID=?,"
					+ "VisitID=?, Priority=?,ReferralDetails=?,Been_Read=? WHERE ID=?");
			patientReferralLoader.loadParameters(ps, r);
			ps.setLong(8, r.getId());
			ps.executeUpdate();
			return DBUtil.getLastInsert(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
	
	/**
	 * Removes the given referral
	 *
	 * param referralID The unique ID of a specific patient Referral
	 * @throws DBException
	 */
	public void removeReferral(long referralID) throws DBException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = factory.getConnection();
			ps = conn.prepareStatement("DELETE FROM PatientReferrals WHERE ID=? ");
			ps.setLong(1, referralID);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException(e);
		} finally {
			DBUtil.closeConnection(conn, ps);
		}
	}
}
